
/* 
---------------------------------------------------------------------------------------
	UPDATE CHARGEMASTER SCRIPT
---------------------------------------------------------------------------------------
Description:
	Updates the chargemaster table with the chargemaster1 table.  When this finishes
	chargermaster = chargermaster1.  The last step is to drop chargemaster1
Pre-requisite:
	You must first copy the chargemaster1 table from phssql298 database= insight_rock
	to the target database where the chargemaster is to be updated.
	
	This script will only execute if the chargemaster1 table was copied from phssql298 
	and has the correct row count of 10913 rows.
---------------------------------------------------------------------------------------
*/
set nocount on

begin try

declare @Chargemaster1Rows int
select @Chargemaster1Rows = count(*) from dbo.Chargemaster1

if object_id('chargemaster1') is not null and @Chargemaster1Rows = 10913 begin

	truncate table dbo.chargemaster

	if  exists(select 1 from sysindexes where name = 'IDX_ChargeMaster_CPTCode') begin
		DROP INDEX dbo.ChargeMaster.IDX_ChargeMaster_CPTCode
	End

	if  exists(select 1 from sysindexes where name = 'IDX_ChargeMaster_Description') begin
		DROP INDEX dbo.ChargeMaster.IDX_ChargeMaster_Description
	End

	if  exists(select 1 from sysindexes where name = 'PK_ChargeMaster') begin
		ALTER TABLE [dbo].[Chargemaster] DROP CONSTRAINT PK_Chargemaster
	End

	insert into chargemaster(CPTCode,[Description],NFprice,Price,Comments)
	select CPTCode,[Description],NFprice,Price,Comments from dbo.chargemaster1 

	if  NOT exists(select 1 from sysindexes where name = 'PK_ChargeMaster') begin
		ALTER TABLE [dbo].[Chargemaster] ADD CONSTRAINT PK_ChargeMaster PRIMARY KEY CLUSTERED ([Id] ASC)
	End

	if  NOT exists(select 1 from sysindexes where name = 'IDX_ChargeMaster_CPTCode') begin
		CREATE NONCLUSTERED INDEX IDX_ChargeMaster_CPTCode ON [dbo].[Chargemaster] ([CPTCode] ASC)
	End

	if  NOT exists(select 1 from sysindexes where name = 'IDX_ChargeMaster_Description') begin
		CREATE NONCLUSTERED INDEX IDX_ChargeMaster_Description ON [dbo].[Chargemaster] ([Description] ASC)
	End

	--Display the table

	select * from dbo.chargemaster
	/*
	Only delete chargemaster1 if this is being executed on a machine other than PHSSQL298.
	*/
	if  @@servername != 'PHSSQL298' drop table dbo.chargemaster1

end

End Try

Begin Catch
Declare 
    @ErrorMessage    NVARCHAR(4000),
    @ErrorNumber     INT,
    @ErrorSeverity   INT,
    @ErrorState      INT,
    @ErrorLine       INT,
    @ErrorProcedure  NVARCHAR(200);

-- Assign variables to error-handling functions that 
-- capture information for RAISERROR.
Select 
    @ErrorNumber = Coalesce(ERROR_NUMBER(),0),
    @ErrorSeverity = Coalesce(ERROR_SEVERITY(),0),
    @ErrorState = Coalesce(ERROR_STATE(),0),
    @ErrorLine = Coalesce(ERROR_LINE(),0),
    @ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');

-- Building the message string that will contain original
-- error information.
Select @ErrorMessage = convert(nvarchar(4000),'Error: '+cast(@ErrorNumber as varchar)+', Level '
			+ cast(@ErrorSeverity as varchar)+', State '+cast(@ErrorState as varchar)+', Procedure '
			+ cast(@ErrorProcedure as varchar)+', Line '+cast(@ErrorLine as varchar)+', ' 
			+ 'Message: '+ coalesce(ERROR_MESSAGE(),'NO MESSAGE'));

--If XACT_STATE() <> 0 Rollback Transaction UpdateChargeMaster

-- Raise an error: msg_str parameter of RAISERROR will contain
-- the original error information.

Raiserror
    (
    @ErrorMessage, 
    @ErrorSeverity, 
    1               
    );

End Catch;